package cn.smvc.sys.dao.impl;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.BeanProcessor;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.RowProcessor;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.springframework.stereotype.Repository;

import cn.smvc.common.dao.JDBCUtils;
import cn.smvc.common.utils.StringUtils;
import cn.smvc.sys.dao.SysUserDao;
import cn.smvc.sys.entity.SysUser;

@Repository("sysUserDao")
public class SysUserDaoImpl implements SysUserDao{

	/*
	 * 这两个是处理数据库表字段下划线的，
	 * 		与实体类中的属性对应（驼峰规则）
	 */
	BeanProcessor bean = new GenerousBeanProcessor();
	RowProcessor processor = new BasicRowProcessor(bean);
	BeanListHandler<SysUser> beanListHandler = new BeanListHandler<SysUser>(SysUser.class, processor);
	BeanHandler<SysUser> beanHandler = new BeanHandler<SysUser>(SysUser.class, processor);
	
	@Override
	public List<SysUser> findAll(SysUser user) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public SysUser findOne(String id) throws SQLException {
		String sql = "select * from sys_user where id=?";
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		SysUser user = null;
		user = queryRunner.query(sql, beanHandler, id);
		return user;
	}

	@Override
	public int save(SysUser user) throws SQLException {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public int update(SysUser user) throws SQLException {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public int delete(String id) throws SQLException {
		String sql = "delete from sys_user  where id=? ";
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		int result = 0;
		try {
			// 用queryRunner保存信息
			result = queryRunner.update(JDBCUtils.getConnection(), 
					sql.toString(), id);
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}
		return result;
	}

	@Override
	public int count(String where) throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		StringBuilder sql = new StringBuilder();
		sql
		.append("select count(*) from sys_user where ")
		.append(where);
		Long count = queryRunner.query(sql.toString(), new ScalarHandler<Long>());
		
		return count != null ? count.intValue() : 0;
	}

	@Override
	public List<SysUser> findBySql(String sql, Object...objects) throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		List<SysUser> list = null;
		list = queryRunner.query(sql, beanListHandler, objects);
		return list;
	}

	@Override
	public List<SysUser> findByWhere(String where) throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		StringBuilder sb = new StringBuilder();
		sb.append("select * from sys_user where ").append(where);
		List<SysUser> list = null;
		list = queryRunner.query(sb.toString(), beanListHandler);
		return list;
	}

	@Override
	public List<SysUser> findPage(String where, int page, int limit, String orderBy) throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		StringBuilder sb = new StringBuilder();
		sb.append("select * from sys_user where ").append(where);
		if(StringUtils.isNotEmpty(orderBy)) {
			sb.append(" order by ").append(orderBy);
		}
		sb.append(" limit ").append((page - 1) * limit).append(", ").append(limit);
		List<SysUser> list = null;
		list = queryRunner.query(sb.toString(), beanListHandler);
		return list;
	}

}
